"""
Case Type   : 全链路跟踪
Case Name   : 支持全链路跟踪,jdbc连接方式，jdbc版本>6.0,server版本<6.0,查看net_trans_time记录
Create At   : 2024/10
Owner       : haomeng
Description :
    1、查看参数默认值
    2、设置enable_record_nettime为on，track_stmt_stat_level为'L0,L0'
    3、导入jdbc脚本并执行部分DDL语句,同时获取server版本信息
    4、conn连接关闭，查看系统视图关键字段信息
    5、查看statement_history视图net_trans_time字段总数
    6、清理环境，恢复参数默认值
Expect      :
    1、参数默认值为off
    2、设置成功
    3、导入成功，执行成功，server版本需小于6.0，否则不执行
    4、statement视图中query的net_trans_time字段与statement_history视图中query的net_trans_time字段总和应一致，且实际只记录最后一条sql耗时
    5、statement_history视图中query的net_trans_time字段总数量与插入的数量一致,默认缓存为0，实际只记录最后一条sql耗时和数量，为20条
    6、恢复环境成功
History     :
"""

import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.ComThread import ComThread
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class JDBCQuikAutoBalance(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.comsh = CommonSH('PrimaryDbUser')
        self.com = Common('PrimaryDbUser')
        self.pri_root = Node('PrimaryRoot')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.param1 = 'enable_record_nettime'
        self.param2 = 'track_stmt_stat_level'
        self.table_name = 't_jdbc_record_nettime_case0019'
        self.script_name = 'test_jdbc_record_nettime_case0019'
        self.dest_path = os.path.join(macro.DB_MY_PATH, 'record_nettime_0019')

    def test_record_nettime(self):
        text = '-----step1:查看enable_record_nettime参数默认值;  expect:默认值为off-----'
        self.log.info(text)

        value = self.comsh.execut_db_sql(f'show {self.param1}', dbname='postgres')
        self.param_default1 = value.splitlines()[-2].strip()
        self.log.info(self.param_default1)
        self.assertEqual('off', self.param_default1, 'nettime参数默认值校验失败')

        self.param_default2 = self.com.show_param(self.param2)
        self.log.info(self.param_default2)
        self.assertIn('OFF,L0', self.param_default2, 'track参数默认值校验失败')

        text = "-----step2:设置enable_record_nettime为on，track_stmt_stat_level为'L0,L0'  expect:设置成功-----"
        self.log.info(text)

        set_param1 = self.comsh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f'{self.param1} = on')
        self.log.info(set_param1)
        self.assertTrue(set_param1, '设置nettime参数失败')

        set_param2 = self.comsh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"{self.param2} = 'L0,L0'")
        self.log.info(set_param2)
        self.assertTrue(set_param2, '设置track参数失败')

        restart_db = self.comsh.restart_db_cluster()
        self.log.info(restart_db)
        self.assertTrue(restart_db)

        text = "-----step3:导入jdbc脚本并执行批量插入语句   expect:导入成功，执行成功-----"
        self.log.info(text)

        self.log.info('-----远程复制java脚本至指定主机-----')
        self.com.scp_file(self.pri_root, f'{self.script_name}.java', self.dest_path)

        cmd1 = f'ls {self.dest_path}'
        res1 = self.pri_root.sh(cmd1).result()
        self.log.info(res1)
        self.assertIn(self.script_name, res1, '远程复制java脚本失败')

        self.log.info('-----编译java文件-----')
        self.pri_root.scp_put(macro.JDBC_PATH, f"{self.dest_path}/postgresql.jar")
        cmd2 = f"javac -encoding utf-8 -cp '{os.path.join(self.dest_path, 'postgresql.jar')}' " \
               f"{os.path.join(self.dest_path, f'{self.script_name}.java')}"
        self.log.info(cmd2)
        res2 = self.pri_root.sh(cmd2).result()
        self.log.info(res2)
        self.assertEqual('', res2, '编译java文件失败')

        self.log.info('-----运行java脚本-----')
        url_host_port = f'{self.pri_user.db_host}:{self.pri_user.db_port}'
        cmd3 = f"java -cp {os.path.join(self.dest_path, 'postgresql.jar')}:{self.dest_path} " \
               f"{self.script_name} {url_host_port} postgres {self.pri_user.db_user} " \
               f"{self.pri_user.db_password}"
        self.log.info(cmd3)
        java_res = self.pri_root.sh(cmd3).result()
        self.log.info(java_res)
        self.assertIn('Connection succeed', java_res, 'java脚本运行报错')

        version_list = ['1.0.0', '2.0.0', '3.0.0', '5.0.0']
        for version in version_list:
            if version in java_res.splitlines()[-1]:
                text = '-----step4:conn连接关闭，查看系统视图关键字段信息  ' \
                       'expect:statement视图中query的net_trans_time字段与statement_history视图中' \
                       'query的net_trans_time字段总和应一致，且实际只记录最后一条sql耗时-----'
                self.log.info(text)

                sql1 = f"select query,db_time,net_trans_time from dbe_perf.statement " \
                       f"where query like '%{self.table_name}%';" \
                       f"select query,db_time,net_trans_time from dbe_perf.statement_history " \
                       f"where query like '%{self.table_name}%';"
                sql2 = f"select sum(net_trans_time) from dbe_perf.statement " \
                       f"where query like '%{self.table_name}%';"
                sql3 = f"select sum(net_trans_time) from dbe_perf.statement_history " \
                       f"where query like '%{self.table_name}%';"

                res1 = self.comsh.execut_db_sql(sql1, dbname='postgres')
                self.log.info(res1)

                res2 = self.comsh.execut_db_sql(sql2, dbname='postgres')
                self.log.info(res2)
                self.assertEqual(0, int(res2.splitlines()[-2]), '查询结果为0')

                res3 = self.comsh.execut_db_sql(sql3, dbname='postgres')
                self.log.info(res3)
                self.assertEqual(0, int(res3.splitlines()[-2]), '查询结果为0')

                sql4 = f"select count(query) from dbe_perf.statement " \
                       f"where query like '%drop table {self.table_name}%';"

                res4 = self.comsh.execut_db_sql(sql4, dbname='postgres')
                self.log.info(res4)
                self.assertEqual(1, int(res4.splitlines()[-2]), '统计到多条SQL')

                text = '-----step5:查看statement_history视图net_trans_time字段总数  ' \
                       'expect:net_trans_time字段总数量与插入的数量一致,实际只记录最后一条sql耗时和数量，缓存默认值为0,总统计数量为20-----'
                self.log.info(text)

                sql5 = f"select count(net_trans_time) from dbe_perf.statement_history " \
                       f"where query like '%drop table {self.table_name}%';"
                res5 = self.comsh.execut_db_sql(sql5, dbname='postgres')
                self.log.info(res5)
                self.assertEqual(20, int(res5.splitlines()[-2]), '查询结果为0')
            else:
                self.log.info('-----server版本不符合要求，退出执行-----')
                break

    def tearDown(self):
        text = '-----step6:恢复参数默认值,恢复环境  expect:恢复成功-----'
        self.log.info(text)

        cmd = f"drop table if exists {self.table_name};" \
              f"select reset_unique_sql('global', 'all', 2);" \
              f"delete from dbe_perf.statement_history;"
        clear_view = self.comsh.execut_db_sql(cmd, dbname='postgres')
        self.log.info(clear_view)

        reset_param1 = self.comsh.execute_gsguc('set',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f'{self.param1} = {self.param_default1}')
        self.log.info(reset_param1)

        reset_param2 = self.comsh.execute_gsguc('set',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.param2} = '{self.param_default2}'")
        self.log.info(reset_param2)

        restart = self.comsh.restart_db_cluster()
        self.log.info(restart)

        reset_param_value1 = self.comsh.execut_db_sql(f'show {self.param1}', dbname='postgres')
        self.log.info(reset_param_value1)
        reset_param_value2 = self.com.show_param(self.param2)
        self.log.info(reset_param_value2)

        self.assertEqual(self.param_default1, reset_param_value1.splitlines()[-2].strip(), '恢复nettime默认值失败')
        self.assertEqual(self.param_default2, reset_param_value2, '恢复track默认值失败')
        self.assertTrue(restart)
        self.log.info(f'----{os.path.basename(__file__)} end----')
